﻿
Public Class AccesoDatos

    Dim conexion As New Object
    Dim cmd As New Object
    Dim string_conexion As String = "Server=Max-PC\SQLEXPRESS;Database=FINANCIERA;Trusted_Connection=yes;"


    Public Sub New()
        conexion = New Data.SqlClient.SqlConnection
        cmd = New Data.SqlClient.SqlCommand
    End Sub

    Public Sub traer_valores_combo(ByRef combo As ComboBox, ByVal tabla As String)
        Dim valorVacioParaCombo As New String("SELECT 0 AS ID, ' ' AS DESCRIPCION FROM " & tabla & " UNION ")

        Dim tablaDB As New Data.DataTable

        Me.conectar()

        Me.cmd.CommandText = valorVacioParaCombo & "SELECT ID, DESCRIPCION FROM " & tabla & " ORDER BY DESCRIPCION"
        tablaDB.Load(Me.cmd.ExecuteReader())

        Me.conexion.Close()

        combo.DataSource = Nothing
        combo.Items.Clear()
        combo.DataSource = tablaDB
        combo.ValueMember = "ID"
        combo.DisplayMember = "DESCRIPCION"
        combo.SelectedIndex = -1

    End Sub

    Public Sub traer_valores_combo_Area(ByRef combo As ComboBox, ByVal tabla As String, ByVal tipoArea As Integer)
        Dim valorVacioParaCombo As New String("SELECT 0 AS ID, ' ' AS DESCRIPCION FROM " & tabla & " UNION ")

        Dim tablaDB As New Data.DataTable

        Me.conectar()

        Me.cmd.CommandText = valorVacioParaCombo & "SELECT ID, DESCRIPCION FROM " & tabla & " WHERE ID_TIPO_AREA_GEOGRAFICA = " & tipoArea & " ORDER BY DESCRIPCION"
        tablaDB.Load(Me.cmd.ExecuteReader())

        Me.conexion.Close()

        combo.DataSource = Nothing
        combo.Items.Clear()
        combo.DataSource = tablaDB
        combo.ValueMember = "ID"
        combo.DisplayMember = "DESCRIPCION"
        combo.SelectedIndex = -1

    End Sub

    Public Sub traer_valores_combo_Estado_Cliente(ByRef combo As ComboBox)
        Dim valorVacioParaCombo As New String("SELECT 0 AS ID, ' ' AS DESCRIPCION FROM ESTADOS_CLIENTES UNION ")

        Dim tablaDB As New Data.DataTable

        Me.conectar()

        Me.cmd.CommandText = valorVacioParaCombo & "SELECT ID, DESCRIPCION FROM ESTADOS_CLIENTES WHERE ID <> 2 ORDER BY DESCRIPCION"
        tablaDB.Load(Me.cmd.ExecuteReader())

        Me.conexion.Close()

        combo.DataSource = Nothing
        combo.Items.Clear()
        combo.DataSource = tablaDB
        combo.ValueMember = "ID"
        combo.DisplayMember = "DESCRIPCION"
        combo.SelectedIndex = -1

    End Sub

    Public Function ejecutarConsulta(ByVal consulta As String) As Data.DataTable
        Dim tablaDB As New Data.DataTable
        Me.conectar()
        Me.cmd.CommandText = consulta
        tablaDB.Load(Me.cmd.ExecuteReader())
        Me.conexion.Close()
        Return tablaDB
    End Function

    Private Sub conectar()
        Me.conexion.ConnectionString = Me.string_conexion
        Me.conexion.Open()
        Me.cmd.Connection = conexion
        Me.cmd.CommandType = CommandType.Text
    End Sub

    Public Sub ejecutarConsultaInforme(ByVal consulta As String, ByRef table As Data.DataTable)
        Me.conectar()
        Me.cmd.CommandText = consulta
        table.Load(Me.cmd.ExecuteReader())
        Me.conexion.Close()
    End Sub
End Class
